package cn.office.apache;

import org.apache.poi.hpsf.DocumentSummaryInformation;
import org.apache.poi.hpsf.SummaryInformation;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

import javax.swing.filechooser.FileSystemView;
import java.io.*;
import java.util.Date;

/**
 * @author leng
 * @date 2020.1.6
 * 操作Excel工具类
 * 类型: Apache poi
 * 功能比较强大,缺点编码不能指定,只能跟随代码编码,在代码编码是utf-8时乱码
 */
public class ExcleUtils {

    /**
     * 新建一个Excel表格
     * @throws IOException
     */
    public static void createExcel() throws IOException {
        // 获取桌面路径
        FileSystemView fsv = FileSystemView.getFileSystemView();
        String desktop = fsv.getHomeDirectory().getPath();
        String filePath = desktop + "/template.xls";

        File file = new File(filePath);
        OutputStream outputStream = new FileOutputStream(file);

        //创建文件类
        HSSFWorkbook workbook = new HSSFWorkbook();

        //创建工作簿
        HSSFSheet sheet = workbook.createSheet("Sheet1");
        HSSFRow row = sheet.createRow(0);
        row.createCell(0).setCellValue("id");
        row.createCell(1).setCellValue(new String("test订单号".getBytes("UTF-8"),"GBK"));
        row.createCell(2).setCellValue("下单时间");
        row.createCell(3).setCellValue("个数");
        row.createCell(4).setCellValue("单价");
        row.createCell(5).setCellValue("订单金额");
        HSSFCell cell = row.createCell(6);
        // 设置行的高度
        row.setHeightInPoints(30);

        HSSFRow row1 = sheet.createRow(1);
        row1.createCell(0).setCellValue("1");
        // 日期格式化
        HSSFCellStyle cellStyle2 = workbook.createCellStyle();
        HSSFCreationHelper creationHelper = workbook.getCreationHelper();
        cellStyle2.setDataFormat(creationHelper.createDataFormat().getFormat("yyyy-MM-dd HH:mm:ss"));
        // 设置列的宽度
        sheet.setColumnWidth(2, 20 * 256);
        HSSFCell cell2 = row1.createCell(2);
        cell2.setCellStyle(cellStyle2);
        cell2.setCellValue(new Date());
        row1.createCell(3).setCellValue(2);


        // 保留两位小数
        HSSFCellStyle cellStyle3 = workbook.createCellStyle();
        cellStyle3.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00"));
        HSSFCell cell4 = row1.createCell(4);
        cell4.setCellStyle(cellStyle3);
        cell4.setCellValue(29.5);
        // 货币格式化
        HSSFCellStyle cellStyle4 = workbook.createCellStyle();
        HSSFFont font = workbook.createFont();
        font.setFontName("华文行楷");
        font.setFontHeightInPoints((short)15);
        font.setColor((short) 11);
        cellStyle4.setFont(font);
        HSSFCell cell5 = row1.createCell(5);
        // 设置计算公式
        cell5.setCellFormula("D2*E2");
        // 获取计算公式的值
        HSSFFormulaEvaluator e = new HSSFFormulaEvaluator(workbook);
        cell5 = e.evaluateInCell(cell5);
        System.out.println(cell5.getNumericCellValue());

        //创建文档信息
        workbook.createInformationProperties();
        //摘要信息
        DocumentSummaryInformation dsi= workbook.getDocumentSummaryInformation();
        //类别
        dsi.setCategory("类别:Excel文件");
        //管理者
        dsi.setManager("管理者:李志伟");
        //公司
        dsi.setCompany("公司:--");
        //摘要信息
        SummaryInformation si = workbook.getSummaryInformation();
        //主题
        si.setSubject("主题:--");
        //标题
        si.setTitle("标题:测试文档");
        //作者
        si.setAuthor("作者:李志伟");
        //备注
        si.setComments("备注:POI测试文档");



        workbook.setActiveSheet(0);
        workbook.write(outputStream);
        outputStream.close();
    }

    /**
     * 示例2：读取Excel，解析数据
     * @throws IOException
     */
    public static void readExcel() throws IOException{
        FileSystemView fsv = FileSystemView.getFileSystemView();
        String desktop = fsv.getHomeDirectory().getPath();
        String filePath = desktop + "/template.xls";

        FileInputStream fileInputStream = new FileInputStream(filePath);
        BufferedInputStream bufferedInputStream = new BufferedInputStream(fileInputStream);
        POIFSFileSystem fileSystem = new POIFSFileSystem(bufferedInputStream);
        HSSFWorkbook workbook = new HSSFWorkbook(fileSystem);
        HSSFSheet sheet = workbook.getSheet("Sheet1");
        //一共有多少行
        int lastRowIndex = sheet.getLastRowNum();
        System.out.println(lastRowIndex);
        //遍历所有的行
        for (int i = 0; i <= lastRowIndex; i++) {
            HSSFRow row = sheet.getRow(i);
            if (row == null) { break; }
            //每一行最后的那一列的位置
            short lastCellNum = row.getLastCellNum();
//            遍历每一列
            for (int j = 0; j < lastCellNum; j++) {
                //得到那个单元格
                HSSFCell cell = row.getCell(j);

                Object value = cell.getStringCellValue();

                System.out.println(value);
            }
        }


        bufferedInputStream.close();
    }

}
